package com.cskaoyan.wordmemorize.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cskaoyan.wordmemorize.auth.StpKit;
import com.cskaoyan.wordmemorize.common.exception.BusinessException;
import com.cskaoyan.wordmemorize.converter.VocConverter;
import com.cskaoyan.wordmemorize.dao.entity.*;
import com.cskaoyan.wordmemorize.dao.mapper.*;
import com.cskaoyan.wordmemorize.dto.admin.*;
import com.cskaoyan.wordmemorize.dto.app.DisplayBookVocDTO;
import com.cskaoyan.wordmemorize.dto.app.DisplayChapterDTO;
import com.cskaoyan.wordmemorize.dto.app.UserBookStatisticsDTO;
import com.cskaoyan.wordmemorize.dto.app.UserExampleSentenceDTO;
import com.cskaoyan.wordmemorize.excel.VocExcelImportListener;
import com.cskaoyan.wordmemorize.excel.VocExcelListener;
import com.cskaoyan.wordmemorize.request.*;
import com.cskaoyan.wordmemorize.service.VocService;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
@Slf4j
public class VocServiceImpl extends ServiceImpl<VocMapper, VocDO> implements VocService {

    @Resource
    VocConverter vocConverter;
    @Autowired
    VocMapper vocMapper;
    @Autowired
    UserFavoriteMapper userFavoriteMapper;
    @Autowired
    VocCategoryMapper vocCategoryMapper;
    @Autowired
    VocChapBookMapper vocChapBookMapper;
    @Autowired
    VocMeaningMapper vocMeaningMapper;
    @Autowired
    ExampleSentenceMapper exampleSentenceMapper;
    @Autowired
    VocBookMapper vocBookMapper;
    @Autowired
    VocChapterMapper vocChapterMapper;
    @Autowired
    UserVocExampleSentenceMapper userVocExampleSentenceMapper;

    // 分页查询单词信息(物料管理-词汇管理)
    @Override
    public PageDTO<VocDTO> getVocabularies(VocPageRequest pageQuery) {
        // 1. 构造分页对象
        Page<VocDO> page = new Page<>(pageQuery.getPageNum(), pageQuery.getCount());

        // 2. 构造主表查询条件：voc 表
        LambdaQueryWrapper<VocDO> vocWrapper = new LambdaQueryWrapper<>();

        // 2.1 关键字过滤（后台管理用）
        if (StringUtils.isNotBlank(pageQuery.getKeyword())) {
            vocWrapper.like(VocDO::getContent, pageQuery.getKeyword());
        }

        // 2.2 书籍/章节过滤：借助子查询 IN SQL
        if (StringUtils.isNotBlank(pageQuery.getBookId()) || StringUtils.isNotBlank(pageQuery.getChapterId())) {
            StringBuilder subSql = new StringBuilder("SELECT voc_id FROM voc_chap_book WHERE is_deleted = 0 ");
            if (StringUtils.isNotBlank(pageQuery.getBookId())) {
                subSql.append("AND book_id = ").append(pageQuery.getBookId()).append(" ");
            }
            if (StringUtils.isNotBlank(pageQuery.getChapterId())) {
                subSql.append("AND chapter_id = ").append(pageQuery.getChapterId());
            }
            vocWrapper.inSql(VocDO::getId, subSql.toString());
        }

        // 3. 执行分页查询：先拿到当页的 VocDO 列表
        Page<VocDO> resultPage = vocMapper.selectPage(page, vocWrapper);
        List<VocDO> vocList = resultPage.getRecords(); // 当前页所有 VocDO

        if (vocList.isEmpty()) {
            // 如果本页无数据，直接返回空的 PageDTO
            return vocConverter.vocList2Page(Collections.emptyList(), resultPage.getTotal());
        }

        // 4. 收集本页所有 vocId（用于批量查询释义/例句/用户例句）
        List<Long> vocIdList = vocList.stream()
                .map(VocDO::getId)
                .collect(Collectors.toList());

        // 5. 批量查询 “单词释义” 列表
        List<VocMeaningDO> allMeanings = vocMeaningMapper.selectList(new LambdaQueryWrapper<VocMeaningDO>()
                .in(VocMeaningDO::getVocId, vocIdList)
                .eq(VocMeaningDO::getIsDeleted, 0)
        );

        // 6. 批量查询 “单词例句” 列表
        List<ExampleSentenceDO> allExamples = exampleSentenceMapper.selectList(new LambdaQueryWrapper<ExampleSentenceDO>()
                .in(ExampleSentenceDO::getVocId, vocIdList)
                .eq(ExampleSentenceDO::getIsDeleted, 0)
                .orderByAsc(ExampleSentenceDO::getContent)
        );

        // 8. 将上面三个列表按照 vocId 分组，方便一对多的回填
        Map<Long, List<VocMeaningDO>> meaningMap = allMeanings.stream()
                .collect(Collectors.groupingBy(VocMeaningDO::getVocId));
        Map<Long, List<ExampleSentenceDO>> exampleMap = allExamples.stream()
                .collect(Collectors.groupingBy(ExampleSentenceDO::getVocId));

        // 9. 回填到每个 VocDO 的 “interpretations”、“examples”、“userExamples” 字段
        for (VocDO voc : vocList) {
            Long vid = voc.getId();
            voc.setInterpretations(meaningMap.getOrDefault(vid, Collections.emptyList()));
            voc.setExamples(exampleMap.getOrDefault(vid, Collections.emptyList()));
        }
        // 10. 最后，通过 MapStruct 的 converter，把 List<VocDO> + total 转成 PageDTO<VocDTO>
        return vocConverter.vocList2Page(vocList, resultPage.getTotal());
    }

    // 添加单词信息(物料管理-词汇管理)
    @Override
    @Transactional
    public void addVoc(VocCommand command) {
        // 将单词的基本信息保存到voc表中
        VocDO vocDO = vocConverter.command2VocDO(command);
        vocMapper.insert(vocDO);

        // 保存关系
        List<VocChapBookDO> relations = vocConverter.commands2VocChapBookDOs(command.getRelations());
        if (relations != null) {
            for (VocChapBookDO vocChapBookDO : relations) {
                // 设置单词id
                vocChapBookDO.setVocId(vocDO.getId());
                vocChapBookMapper.insert(vocChapBookDO);

                // 修改书籍和章节关联的单词数量
                if (vocChapBookDO.getChapterId() != null) {
                    // 关联到了一个章节
                    LambdaUpdateWrapper<VocChapterDO> vocChapterWrapper = new LambdaUpdateWrapper<>();
                    vocChapterWrapper.eq(VocChapterDO::getId, vocChapBookDO.getChapterId())
                            // set voc_count = voc_count + 1
                            .setSql("voc_count = voc_count + 1");

                    vocChapterMapper.update(null, vocChapterWrapper);
                }

                // 更新书籍关联的单词数量
                // 关联到了一个章节
                LambdaUpdateWrapper<VocBookDO> vocBookWrapper = new LambdaUpdateWrapper<>();
                vocBookWrapper.eq(VocBookDO::getId, vocChapBookDO.getBookId())
                        // set voc_count = voc_count + 1
                        .setSql("voc_count = voc_count + 1");
                vocBookMapper.update(null, vocBookWrapper);

            }
        }
        // 保存释义
        List<VocMeaningCommand> interpretations = command.getInterpretations();
        if (interpretations != null) {
            List<VocMeaningDO> vocMeaningDOs = vocConverter.commands2VocMeaningDOs(interpretations);
            for (VocMeaningDO vocMeaningDO : vocMeaningDOs) {
                vocMeaningDO.setVocId(vocDO.getId());
                vocMeaningMapper.insert(vocMeaningDO);

            }
        }
        // 保存例句
        List<ExampleSentenceCommand> examples = command.getExamples();
        if (examples != null) {
            List<ExampleSentenceDO> exampleSentenceDOS = vocConverter.commands2ExampleSentenceDOs(examples);
            for (ExampleSentenceDO exampleSentenceDO : exampleSentenceDOS) {
                exampleSentenceDO.setVocId(vocDO.getId());
                exampleSentenceMapper.insert(exampleSentenceDO);
            }
        }
    }

    // 更新单词信息(物料管理-词汇管理)
    @Override
    @Transactional
    public void updateVoc(VocCommand command) {
        // 1. 更新单词基本信息
        VocDO vocDO = vocConverter.command2VocDO(command);
        vocMapper.updateById(vocDO);
        Long vocId = vocDO.getId();

        // 2. 处理释义
        List<VocMeaningCommand> interpretations = command.getInterpretations();
        if (interpretations != null && !interpretations.isEmpty()) {
            for (VocMeaningCommand meaning : interpretations) {
                VocMeaningDO meaningDO = vocConverter.command2VocMeaningDO(meaning);
                meaningDO.setVocId(vocId);
                if (meaningDO.getId() != null) {
                    vocMeaningMapper.updateById(meaningDO);
                } else {
                    vocMeaningMapper.insert(meaningDO);
                }
            }
        }

        // 3. 处理例句
        List<ExampleSentenceCommand> examples = command.getExamples();
        if (examples != null && !examples.isEmpty()) {
            for (ExampleSentenceCommand example : examples) {
                ExampleSentenceDO exampleDO = vocConverter.command2ExampleSentenceDO(example);
                exampleDO.setVocId(vocId);
                if (exampleDO.getId() != null) {
                    exampleSentenceMapper.updateById(exampleDO);
                } else {
                    exampleSentenceMapper.insert(exampleDO);
                }
            }
        }

        // 4. 处理书籍章节关系（需要特殊处理数量变化）
        List<VocChapBookCommand> newRelations = command.getRelations();
        if (newRelations != null && !newRelations.isEmpty()) {
            // 获取数据库中该单词现有的关系
            LambdaQueryWrapper<VocChapBookDO> relationWrapper = new LambdaQueryWrapper<>();
            relationWrapper.eq(VocChapBookDO::getVocId, vocId);
            List<VocChapBookDO> oldRelations = vocChapBookMapper.selectList(relationWrapper);
            Map<Long, VocChapBookDO> oldRelationMap = oldRelations.stream()
                    .collect(Collectors.toMap(VocChapBookDO::getId, Function.identity()));

            // 处理新旧关系对比
            for (VocChapBookCommand newRel : newRelations) {
                // 使用转换器方法转换关系对象
                VocChapBookDO newRelationDO = vocConverter.command2VocChapBookDO(newRel);
                newRelationDO.setVocId(vocId);

                if (newRelationDO.getId() != null) {
                    // 更新操作：检查书籍/章节是否变化
                    VocChapBookDO oldRelation = oldRelationMap.get(newRelationDO.getId());
                    if (oldRelation != null) {
                        // 检查书籍或章节是否变化
                        boolean bookChanged = !Objects.equals(oldRelation.getBookId(), newRelationDO.getBookId());
                        boolean chapterChanged = !Objects.equals(oldRelation.getChapterId(), newRelationDO.getChapterId());

                        if (bookChanged || chapterChanged) {
                            // 旧关系减少计数
                            decrementCounts(oldRelation.getBookId(), oldRelation.getChapterId());
                            // 新关系增加计数
                            incrementCounts(newRelationDO.getBookId(), newRelationDO.getChapterId());
                        }
                        // 更新关系记录
                        vocChapBookMapper.updateById(newRelationDO);
                        // 从旧关系映射中移除已处理项
                        oldRelationMap.remove(oldRelation.getId());
                    }
                } else {
                    // 新增操作
                    vocChapBookMapper.insert(newRelationDO);
                    incrementCounts(newRelationDO.getBookId(), newRelationDO.getChapterId());
                }
            }
        }
    }


    // 删除单词信息(物料管理-词汇管理)
    @Override
    @Transactional
    public DeleteVocOperateDTO deleteVocabulary(Long vocId) {
        // 1. 软删 voc 表
        VocDO vocDO = vocMapper.selectById(vocId);
        if (vocDO == null || vocDO.getIsDeleted() == 1) {
            // 返回删除失败或已删除提示
            throw new BusinessException("单词不存在",500);
        }

        vocMapper.deleteById(vocDO);

        // 2. 删除 voc_chap_book 关系并更新章节/书籍数量
        List<VocChapBookDO> relations = vocChapBookMapper.selectList(
                new LambdaQueryWrapper<VocChapBookDO>().eq(VocChapBookDO::getVocId, vocId));
        for (VocChapBookDO relation : relations) {
            vocChapBookMapper.deleteById(relation.getId());

            // 更新章节数量
            if (relation.getChapterId() != null) {
                vocChapterMapper.update(
                        null,
                        new LambdaUpdateWrapper<VocChapterDO>()
                                .eq(VocChapterDO::getId, relation.getChapterId())
                                .setSql("voc_count = voc_count - 1")
                );
            }
            // 更新书籍数量
            vocBookMapper.update(
                    null,
                    new LambdaUpdateWrapper<VocBookDO>()
                            .eq(VocBookDO::getId, relation.getBookId())
                            .setSql("voc_count = voc_count - 1")
            );
        }

        // 3. 删除释义、例句
        vocMeaningMapper.delete(new LambdaQueryWrapper<VocMeaningDO>().eq(VocMeaningDO::getVocId, vocId));
        exampleSentenceMapper.delete(new LambdaQueryWrapper<ExampleSentenceDO>().eq(ExampleSentenceDO::getVocId, vocId));

        // 4. 返回操作状态
        return new DeleteVocOperateDTO(vocDO.getId(),vocDO.getContent());
    }

    // 批量删除单词信息(物料管理-词汇管理)
    @Override
    @Transactional
    public List<DeleteVocOperateDTO> deleteVocabularies(BatchDeleteCommand command) {
        List<DeleteVocOperateDTO> results = new ArrayList<>();
        // 转换String ID为Long
        List<Long> longIds = command.getIds().stream()
                .map(id -> Long.parseLong(id))
                .collect(Collectors.toList());
        // 处理每个单词ID
        for (Long vocId : longIds) {
                // 调用单个删除方法
                DeleteVocOperateDTO result = deleteVocabulary(vocId);
                results.add(result);
        }
        return results;
    }

    // 获取书籍-章节-单词关系列表(物料管理-词汇管理)
    @Override
    public List<VocChapBookDTO> getVocRelations(Long vocId) {
        List<VocChapBookDTO> resultList = new ArrayList<>();

        //查询单词与章节、书籍关联关系
        LambdaQueryWrapper<VocChapBookDO> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VocChapBookDO::getVocId,vocId)
                .eq(VocChapBookDO::getIsDeleted,0);//过滤未删除记录
        List<VocChapBookDO> relations = vocChapBookMapper.selectList(queryWrapper);
         if (relations.isEmpty()){
             return Collections.emptyList();
         }
         //提取所有章节id和书籍id，用于批量查询
         List<Long> chapterIds = relations.stream()
                 .map(VocChapBookDO::getChapterId)
                 .filter(Objects::nonNull)
                 .distinct()
                 .collect(Collectors.toList());
         List<Long> bookIds = relations.stream()
                 .map(VocChapBookDO::getBookId)
                 .distinct()
                 .collect(Collectors.toList());
         //批量查询章节和书籍信息
        Map<Long, VocChapterDO> chapterMap = chapterIds.isEmpty() ?
                Collections.emptyMap() :
                vocChapterMapper.selectBatchIds(chapterIds)
                        .stream()
                        .collect(Collectors.toMap(VocChapterDO::getId, chapter -> chapter));

        Map<Long, VocBookDO> bookMap = bookIds.isEmpty() ?
                Collections.emptyMap() :
                vocBookMapper.selectBatchIds(bookIds)
                        .stream()
                        .collect(Collectors.toMap(VocBookDO::getId, book -> book));
        //提取所有类目id，批量查询类目信息
        List<Long> categoryIds = bookMap.values().stream()
                .map(VocBookDO::getBcId)
                .distinct()
                .collect(Collectors.toList());
        Map<Long, VocCategoryDO> categoryMap = categoryIds.isEmpty() ?
                Collections.emptyMap() :
                vocCategoryMapper.selectBatchIds(categoryIds)
                        .stream()
                        .collect(Collectors.toMap(VocCategoryDO::getId, category -> category));
        //  组装结果
        for (VocChapBookDO relation : relations) {
            VocChapBookDTO dto = new VocChapBookDTO();

            // 设置关系ID和单词信息
            dto.setId(relation.getId().toString());
            dto.setVocId(vocId.toString());
            dto.setVocName(relation.getVocName());

            // 设置章节信息
            if (relation.getChapterId() != null) {
                VocChapterDO chapterDO = chapterMap.get(relation.getChapterId());
                if (chapterDO != null) {
                    dto.setChapterId(chapterDO.getId().toString());
                    dto.setChapterName(chapterDO.getChapterName());

                    // 设置书籍信息
                    VocBookDO bookDO = bookMap.get(chapterDO.getBookId());
                    if (bookDO != null) {
                        dto.setBookId(bookDO.getId().toString());
                        dto.setBookName(bookDO.getName());

                        // 设置类目信息
                        VocCategoryDO categoryDO = categoryMap.get(bookDO.getBcId());
                        if (categoryDO != null) {
                            dto.setCategoryId(categoryDO.getId().toString());
                            dto.setCategoryName(categoryDO.getCategoryName());
                        }
                    }
                }
            }
            resultList.add(dto);
        }
        return resultList;
    }

    // APP端获取无章节的书籍单词列表
    @Override
    public PageDTO<DisplayBookVocDTO> getDisplayBookVocsInfo(AppVocPageRequest pageRequest) {
         //  参数校验
        if (StringUtils.isBlank(pageRequest.getUserId())){
            throw new BusinessException("用户ID不能为空",500);
        }
        if (StringUtils.isBlank(pageRequest.getBookId())) {
            throw new BusinessException("书籍ID不能为空",500);
        }
        if (pageRequest.getPageNum() == null || pageRequest.getPageNum() <= 0) {
            pageRequest.setPageNum(1); // 默认第一页
        }
        if (pageRequest.getCount() == null || pageRequest.getCount() <= 0) {
            pageRequest.setCount(20); // 默认每页20条
        }

        // 转换参数类型
        Long userId = Long.parseLong(pageRequest.getUserId());
        Long bookId = Long.parseLong(pageRequest.getBookId());
        Integer pageNum = pageRequest.getPageNum();
        Integer count = pageRequest.getCount();

        // 计算偏移量
        int offset = (pageNum - 1) * count;

        // 查询当前页的单词数据
        List<VocDO> vocList = vocMapper.getVocsOfUserBook(userId, bookId, offset, count);

        // 查询满足条件的总记录数
        QueryWrapper<VocChapBookDO> countWrapper = new QueryWrapper<>();
        countWrapper.eq("book_id", bookId)
                .isNull("chapter_id")
                .eq("is_deleted", 0);
        Long totalCount = vocChapBookMapper.selectCount(countWrapper);

        // 转换为DTO
        List<DisplayBookVocDTO> dtoList = vocConverter.vocDos2DisplayChapterVocDTOs(vocList);

        // 创建分页结果对象并设置属性
        PageDTO<DisplayBookVocDTO> pageDTO = new PageDTO<>();
        pageDTO.setPageList(dtoList);
        pageDTO.setTotalCount(totalCount);

        return pageDTO;
    }

    // APP端获取有章节的书籍单词列表
    @Override
    public PageDTO<DisplayChapterDTO> getDisplayBookChapterVocsInfo(AppVocPageRequest pageRequest) {
        // 参数校验
        if (pageRequest == null || pageRequest.getUserId() == null || pageRequest.getBookId() == null) {
            throw new IllegalArgumentException("参数不能为空");
        }

        // 更灵活的分页参数处理
        if (pageRequest.getPageNum() == null || pageRequest.getPageNum() < 0) {
            pageRequest.setPageNum(1); // 默认为第一页
        }
        if (pageRequest.getCount() == null || pageRequest.getCount() < 0) {
            pageRequest.setCount(10); // 默认每页10条
        }

        // 转换参数类型
        Long userId = Long.parseLong(pageRequest.getUserId());
        Long bookId = Long.parseLong(pageRequest.getBookId());
        int pageNum = pageRequest.getPageNum();
        int count = pageRequest.getCount();

        // 计算偏移量
        int offset = (pageNum - 1) * count;

        // 1. 获取总章节数（用于分页）
        Long totalChapters = getTotalChaptersByBookId(bookId);

        // 2. 分页查询章节数据
        List<VocChapterDO> chapterDOs;
        if (count == 0) {
            // count=0 表示获取所有数据
            chapterDOs = vocMapper.getChapterVocsOfUserBook(userId, bookId, null, null);
        } else {
            chapterDOs = vocMapper.getChapterVocsOfUserBook(userId, bookId, offset, count);
        }

        // 3. 使用转换器将DO转换为DTO
        List<DisplayChapterDTO> chapterDTOs = vocConverter.vocChapterDOs2displayChapterDTOs(chapterDOs);

        // 4. 构造分页结果
        PageDTO<DisplayChapterDTO> result = new PageDTO<>();
        result.setPageList(chapterDTOs);
        result.setTotalCount(totalChapters);

        return result;
    }

    // APP端获取单词详情
    @Override
    public VocDTO getVocDetail(Long vocId, Long userId) {
         //查询基础单词信息
        VocDO vocDO = vocMapper.selectById(vocId);
        if (vocDO==null){
            return null;
        }
        //设置用户状态
        vocDO.setReviewed(0);
        vocDO.setNotRemember(0);
        vocDO.setFavorite(0);
        //查询单词释义
        LambdaQueryWrapper<VocMeaningDO> meaningWrapper = new LambdaQueryWrapper<>();
        meaningWrapper.eq(VocMeaningDO::getVocId,vocId);
        List<VocMeaningDO> meanings = vocMeaningMapper.selectList(meaningWrapper);
        vocDO.setInterpretations(meanings);
        //查询系统例句
        LambdaQueryWrapper<ExampleSentenceDO> exampleWrapper = new LambdaQueryWrapper<>();
        exampleWrapper.eq(ExampleSentenceDO::getVocId, vocId);
        List<ExampleSentenceDO> examples = exampleSentenceMapper.selectList(exampleWrapper);
        vocDO.setExamples(examples);
        //查询用户自建例句
        LambdaQueryWrapper<UserVocExampleSentenceDO> userExampleWrapper = new LambdaQueryWrapper<>();
        userExampleWrapper.eq(UserVocExampleSentenceDO::getVocId,vocId)
                .eq(userId!=null, UserVocExampleSentenceDO::getUserId,userId);
        List<UserVocExampleSentenceDO> userExamples = userVocExampleSentenceMapper.selectList(userExampleWrapper);
        vocDO.setUserExamples(userExamples);
        //转换DTO
        VocDTO vocDTO = new VocDTO();

        vocDTO.setId(vocDO.getId().toString());
        vocDTO.setContent(vocDO.getContent());
        vocDTO.setUkPhonetic(vocDO.getUkPhonetic());
        vocDTO.setUsPhonetic(vocDO.getUsPhonetic());
        vocDTO.setUsPronounciation(vocDO.getUsPronounciation());
        vocDTO.setUkPronounciation(vocDO.getUkPronounciation());
        vocDTO.setReviewed(vocDO.getReviewed());
        vocDTO.setNotRemember(vocDO.getNotRemember());
        vocDTO.setFavorite(vocDO.getFavorite());
        //转换释义列表
        if (meanings!=null){
            List<VocMeaningDTO> meaningDTOS = meanings.stream()
                    .map(m->{
                        VocMeaningDTO dto = new VocMeaningDTO();
                        dto.setId(m.getId().toString());
                        dto.setVocId(m.getVocId().toString());
                        dto.setType(m.getType());
                        dto.setInterContent(m.getInterContent());
                        return dto;
                    })
                    .collect(Collectors.toList());
            vocDTO.setInterpretations(meaningDTOS);
        }
        // 转换系统例句
        if (examples != null) {
            List<ExampleSentenceDTO> exampleDTOs = examples.stream()
                    .map(e -> {
                        ExampleSentenceDTO dto = new ExampleSentenceDTO();
                        dto.setId(e.getId().toString());
                        dto.setVocId(e.getVocId().toString());
                        dto.setContent(e.getContent());
                        dto.setMeaning(e.getMeaning());
                        return dto;
                    })
                    .collect(Collectors.toList());
            vocDTO.setExamples(exampleDTOs);
        }

        // 转换用户自建例句
        if (userExamples != null) {
            List<UserExampleSentenceDTO> userExampleDTOs = userExamples.stream()
                    .map(u -> {
                        UserExampleSentenceDTO dto = new UserExampleSentenceDTO();
                        dto.setId(u.getId().toString());
                        dto.setUserId(u.getUserId().toString());
                        dto.setVocId(u.getVocId().toString());
                        dto.setSentence(u.getSentence());
                        dto.setMeaning(u.getMeaning());
                        dto.setVocContent(vocDO.getContent()); // 设置关联单词内容
                        return dto;
                    })
                    .collect(Collectors.toList());
            vocDTO.setUserExamples(userExampleDTOs);
        }

        return vocDTO;
    }

    // APP端目标书籍已选单词，未选单词数据
    @Override
    public UserBookStatisticsDTO getUserChooseCountInBook(Long userId, Long bookId) {
        UserBookStatisticsDTO statistics = new UserBookStatisticsDTO();
        try {
            // 1. 查询书籍中的总单词数
            QueryWrapper<VocChapBookDO> totalWrapper = new QueryWrapper<>();
            totalWrapper.select("COUNT(DISTINCT voc_id) as total")
                    .eq("book_id", bookId);
            Map<String, Object> totalMap = vocChapBookMapper.selectMaps(totalWrapper).get(0);
            int total = ((Number) totalMap.get("total")).intValue();
            statistics.setTotal(total);

            // 2. 查询用户已选单词数
            QueryWrapper<UserFavoriteDO> selectedWrapper = new QueryWrapper<>();
            selectedWrapper.select("COUNT(DISTINCT voc_id) as selected")
                    .inSql("voc_id",
                            "SELECT DISTINCT voc_id FROM voc_chap_book WHERE book_id = " + bookId)
                    .eq("user_id", userId)
                    .eq("selected", 1);

            Map<String, Object> selectedMap = userFavoriteMapper.selectMaps(selectedWrapper).get(0);
            int selected = selectedMap.get("selected") != null ?
                    ((Number) selectedMap.get("selected")).intValue() : 0;
            statistics.setSelected(selected);

            // 3. 计算未选单词数
            statistics.setUnSelected(total - selected);

        } catch (Exception e) {
            statistics.setTotal(0);
            statistics.setSelected(0);
            statistics.setUnSelected(0);
            log.error("获取书籍统计信息失败: userId={}, bookId={}", userId, bookId, e);
        }

        return statistics;
    }

    // 校验上传单词excel文件(后端-物料管理-词汇管理)
    @Override
    @Transactional
    public void vocExcelValidate(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(),
                 VocExcelDTO.class,
                 new VocExcelListener(vocMapper, vocChapBookMapper))
                .sheet()
                .doRead();

    }

    // excel文件上传单词(后端-物料管理-词汇管理)
    @Override
    @Transactional
    public ExcelAddOperateDTO addVocExcel(MultipartFile file, Long userId) throws IOException {
        ExcelAddOperateDTO result = new ExcelAddOperateDTO();
        result.setFileName(file.getOriginalFilename());

       InputStream in = file.getInputStream();
        VocExcelImportListener importListener = new VocExcelImportListener(
                vocConverter, vocMapper,vocChapBookMapper,
                vocMeaningMapper, exampleSentenceMapper,
                vocBookMapper,vocChapterMapper);
        EasyExcel.read(in, VocExcelDTO.class,importListener )
                    .sheet()
                    .doRead();

       result.setTotalCount(importListener.getTotalCount());

        return result;
    }

    // 删除单词音频文件(后端-物料管理-词汇管理)
    @Override
    @Transactional
    public DeleteVocFileOperateDTO deleteFile(Long id, String type, String name) {

        return null;
    }

    // 删除书籍-章节-单词关系(后端-物料管理-词汇管理)
    @Override
    @Transactional
    public DeleteVocRelationOperateDTO deleteVocRelation(Long id) {
        // 查询关联关系
        VocChapBookDO relation = vocChapBookMapper.selectById(id);

        //查询单词信息
        VocDO vocDO = vocMapper.selectById(relation.getVocId());

        // 查询章节信息（如果存在）
        VocChapterDO chapterDO = null;
        if (relation.getChapterId() != null) {
            chapterDO = vocChapterMapper.selectById(relation.getChapterId());
        }

        //查询书籍信息（用于更新计数和返回结果）
        VocBookDO bookDO = vocBookMapper.selectById(relation.getBookId());

        // 删除关联关系
        vocChapBookMapper.deleteById(id);

        //更新章节单词计数（如果有关联章节）
        if (chapterDO != null) {
            LambdaUpdateWrapper<VocChapterDO> chapterWrapper = new LambdaUpdateWrapper<>();
            chapterWrapper.eq(VocChapterDO::getId, chapterDO.getId())
                    .setSql("voc_count = voc_count - 1");
            vocChapterMapper.update(null, chapterWrapper);
        }

        // 更新书籍单词计数
        LambdaUpdateWrapper<VocBookDO> bookWrapper = new LambdaUpdateWrapper<>();
        bookWrapper.eq(VocBookDO::getId, bookDO.getId())
                .setSql("voc_count = voc_count - 1");
        vocBookMapper.update(null, bookWrapper);

        //  构建并返回操作结果
        return new DeleteVocRelationOperateDTO(
                id,                          // id
                relation.getVocId(),           // vocId
                vocDO.getContent(),            // vocName
                bookDO.getId(),                // bookId
                bookDO.getName(),              // bookName
                chapterDO != null ? chapterDO.getId() : null, // chapterId
                chapterDO != null ? chapterDO.getChapterName() : null // chapterName
        );
    }

    // 删除单词释义(后端-物料管理-词汇管理)
    @Override
    @Transactional
    public DeleteInterpretationOperateDTO deleteInterpretation(Long id) {
        //检查释义是否存在
        VocMeaningDO vocMeaningDO = vocMeaningMapper.selectById(id);
        if (vocMeaningDO==null){
            throw new BusinessException("释义不存在", 500);
        }
        // 2. 检查单词是否存在
        VocDO vocDO = vocMapper.selectById(vocMeaningDO.getVocId());
        if (vocDO == null) {
            throw new BusinessException("单词不存在", 500);
        }
        vocMeaningMapper.deleteById(vocMeaningDO);
        return new DeleteInterpretationOperateDTO(
               vocMeaningDO.getId(),
                vocMeaningDO.getType(),
                vocMeaningDO.getInterContent(),
                vocDO.getId(),
                vocDO.getContent()
        );
    }

    // 删除单词例句(后端-物料管理-词汇管理)
    @Override
    @Transactional
    public DeleteVocExampleOperateDTO deleteExample(Long id) {
        //检查例句是否存在
        ExampleSentenceDO sentenceDO = exampleSentenceMapper.selectById(id);
        if (sentenceDO==null){
            throw new BusinessException("例句不存在", 500);
        }
        // 检查单词是否存在
        VocDO vocDO = vocMapper.selectById(sentenceDO.getVocId());
        if (vocDO == null) {
            throw new BusinessException("单词不存在", 500);
        }
        exampleSentenceMapper.deleteById(sentenceDO);

        return new DeleteVocExampleOperateDTO(
                id,
                sentenceDO.getContent(),
                sentenceDO.getVocId(),
                vocDO.getContent());
    }

    // 辅助方法：增加书籍/章节的单词计数
    private void incrementCounts(Long bookId, Long chapterId) {
        if (bookId != null) {
            vocBookMapper.update(null, new LambdaUpdateWrapper<VocBookDO>()
                    .eq(VocBookDO::getId, bookId)
                    .setSql("voc_count = voc_count + 1"));
        }
        if (chapterId != null) {
            vocChapterMapper.update(null, new LambdaUpdateWrapper<VocChapterDO>()
                    .eq(VocChapterDO::getId, chapterId)
                    .setSql("voc_count = voc_count + 1"));
        }
    }

    // 辅助方法：减少书籍/章节的单词计数
    private void decrementCounts(Long bookId, Long chapterId) {
        if (bookId != null) {
            vocBookMapper.update(null, new LambdaUpdateWrapper<VocBookDO>()
                    .eq(VocBookDO::getId, bookId)
                    .setSql("voc_count = voc_count - 1"));
        }
        if (chapterId != null) {
            vocChapterMapper.update(null, new LambdaUpdateWrapper<VocChapterDO>()
                    .eq(VocChapterDO::getId, chapterId)
                    .setSql("voc_count = voc_count - 1"));
        }
    }

    private Long getTotalChaptersByBookId(Long bookId) {
        QueryWrapper<VocChapBookDO> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("COUNT(DISTINCT chapter_id) AS chapter_count")
                .eq("book_id", bookId)
                .eq("is_deleted", 0);

        List<Map<String, Object>> result = vocChapBookMapper.selectMaps(queryWrapper);

        if (result != null && !result.isEmpty()) {
            Object count = result.get(0).get("chapter_count");
            return ((Number) count).longValue();
        }
        return 0L;
    }
}